/**
 *
 */
package vn.edu.hungvuongaptech.dao;

import java.io.InputStream;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import vn.edu.hungvuongaptech.common.Constant;
import vn.edu.hungvuongaptech.model.ProcessModel;
import vn.edu.hungvuongaptech.model.ProductModel;
import vn.edu.hungvuongaptech.model.ProjectModel;
import vn.edu.hungvuongaptech.model.StatusModel;
import vn.edu.hungvuongaptech.model.TimeSheetModel;
import vn.edu.hungvuongaptech.model.UserModel;
import vn.edu.hungvuongaptech.model.WorkModel;
import vn.edu.hungvuongaptech.util.DataUtil;

/**
 * @author ThanhHQ
 *
 */
public class TimeSheetDAO {

    public static ArrayList<TimeSheetModel> getTimeSheetByUserId(long userId) {
        ArrayList<TimeSheetModel> timeSheetModels = new ArrayList<TimeSheetModel>();
        TimeSheetModel timeSheetModel = null;
        try {
			PreparedStatement preparedStatement = DataUtil
					.getConnection()
					.prepareStatement(
							Constant.SQL_RES
									.getString("timesheet.sql.getTimeSheetByUserId"));
            preparedStatement.setLong(1, userId);
            ResultSet rs = preparedStatement.executeQuery();
            while (rs.next()) {
                ProductModel productModel = new ProductModel();
                productModel.setProductName(rs.getString("ProductName"));
                WorkModel workModel = new WorkModel();
                workModel.setWorkName(rs.getString("WorkName"));
                ProcessModel processModel = new ProcessModel();
                processModel.setProcessName(rs.getString("ProcessName"));
                StatusModel statusModel = new StatusModel();
                statusModel.setStatusName(rs.getString("StatusName"));
                ProjectModel projectModel = new ProjectModel();
                projectModel.setProjectName(rs.getString("ProjectName"));
                timeSheetModel = new TimeSheetModel();
                timeSheetModel.setTimeSheetId(rs.getLong("TimeSheetID"));
                timeSheetModel.setDate(rs.getString("Date"));
                timeSheetModel.setTime(rs.getDouble("Time"));
                timeSheetModel.setDescription(rs.getString("Description"));
                timeSheetModel.setProduct(productModel);
                timeSheetModel.setWork(workModel);
                timeSheetModel.setProcess(processModel);
                timeSheetModel.setStatus(statusModel);
                timeSheetModel.setProject(projectModel);
                timeSheetModels.add(timeSheetModel);
            }
            return timeSheetModels;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    public static TimeSheetModel getTimeSheetById(long id) {
        TimeSheetModel timeSheetModel = null;
        try {
			PreparedStatement preparedStatement = DataUtil
					.getConnection()
					.prepareStatement(
							Constant.SQL_RES
									.getString("timesheet.sql.getTimeSheetById"));
            preparedStatement.setLong(1, id);
            ResultSet rs = preparedStatement.executeQuery();
            while (rs.next()) {
                ProductModel productModel = new ProductModel();
                productModel.setProductName(rs.getString("ProductName"));
                WorkModel workModel = new WorkModel();
                workModel.setWorkName(rs.getString("WorkName"));
                ProcessModel processModel = new ProcessModel();
                processModel.setProcessName(rs.getString("ProcessName"));
                StatusModel statusModel = new StatusModel();
                statusModel.setStatusName(rs.getString("StatusName"));
                ProjectModel projectModel = new ProjectModel();
                projectModel.setProjectName(rs.getString("ProjectName"));
                timeSheetModel = new TimeSheetModel();
                timeSheetModel.setTimeSheetId(rs.getLong("TimeSheetID"));
                timeSheetModel.setDate(rs.getString("Date"));
                timeSheetModel.setTime(rs.getDouble("Time"));
                timeSheetModel.setDescription(rs.getString("Description"));
                timeSheetModel.setProduct(productModel);
                timeSheetModel.setWork(workModel);
                timeSheetModel.setProcess(processModel);
                timeSheetModel.setStatus(statusModel);
                timeSheetModel.setProject(projectModel);
            }
            return timeSheetModel;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    public static ArrayList<TimeSheetModel> getTimeSheetByRoleAndStatus(int roleId, int statusId) {
        ArrayList<TimeSheetModel> timeSheetModels = new ArrayList<TimeSheetModel>();
        TimeSheetModel timeSheetModel = null;
        try {
			PreparedStatement statement = DataUtil
					.getConnection()
					.prepareStatement(
							Constant.SQL_RES
									.getString("timesheet.sql.getTimeSheetByRoleAndStatus"));
            statement.setInt(1, roleId);
            statement.setInt(2, statusId);
            ResultSet rs = statement.executeQuery();
            while (rs.next()) {
                ProductModel productModel = new ProductModel();
                productModel.setProductName(rs.getString("ProductName"));
                WorkModel workModel = new WorkModel();
                workModel.setWorkName(rs.getString("WorkName"));
                ProcessModel processModel = new ProcessModel();
                processModel.setProcessName(rs.getString("ProcessName"));
                StatusModel statusModel = new StatusModel();
                statusModel.setStatusName(rs.getString("StatusName"));
                ProjectModel projectModel = new ProjectModel();
                projectModel.setProjectName(rs.getString("ProjectName"));
                UserModel userModel = new UserModel();
                userModel.setUsername(rs.getString("Username"));
                timeSheetModel = new TimeSheetModel();
                timeSheetModel.setTimeSheetId(rs.getLong("TimeSheetID"));
                timeSheetModel.setDate(rs.getString("Date"));
                timeSheetModel.setTime(rs.getDouble("Time"));
                timeSheetModel.setDescription(rs.getString("Description"));
                timeSheetModel.setProduct(productModel);
                timeSheetModel.setWork(workModel);
                timeSheetModel.setProcess(processModel);
                timeSheetModel.setStatus(statusModel);
                timeSheetModel.setProject(projectModel);
                timeSheetModel.setUser(userModel);
                timeSheetModels.add(timeSheetModel);
            }
            return timeSheetModels;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    public static ArrayList<TimeSheetModel> getTimeSheetByCondition(long userId, long projectId,
            int statusId, String fromDate, String toDate,
			int descriptionLength, int pageNumber,int itemsPerPage) {
        ArrayList<TimeSheetModel> timeSheetModels = new ArrayList<TimeSheetModel>();
        TimeSheetModel timeSheetModel = null;
        try {
			CallableStatement cstm = DataUtil.getConnection()
										.prepareCall("{ call usp_GetTimeSheetByCondition(?, ?, ?, ?, ?) }");
            cstm.setLong(1, userId);
            cstm.setLong(2, projectId);
            cstm.setInt(3, statusId);
            cstm.setString(4, fromDate);
            cstm.setString(5, toDate);
            ResultSet rs = DataUtil.executeStore(cstm);
            while (rs.next()) {
                ProductModel productModel = new ProductModel();
                productModel.setProductName(rs.getString("ProductName"));
                WorkModel workModel = new WorkModel();
                workModel.setWorkName(rs.getString("WorkName"));
                ProcessModel processModel = new ProcessModel();
                processModel.setProcessName(rs.getString("ProcessName"));
                StatusModel statusModel = new StatusModel();
                statusModel.setStatusName(rs.getString("StatusName"));
                ProjectModel projectModel = new ProjectModel();
                projectModel.setProjectName(rs.getString("ProjectName"));
                UserModel userModel = new UserModel();
                userModel.setUsername(rs.getString("Username"));
                timeSheetModel = new TimeSheetModel();
                timeSheetModel.setTimeSheetId(rs.getLong("TimeSheetID"));
                timeSheetModel.setDate(rs.getString("Date"));
                timeSheetModel.setTime(rs.getDouble("Time"));
                timeSheetModel.setDescription(rs.getString("Description"));
                timeSheetModel.setProduct(productModel);
                timeSheetModel.setWork(workModel);
                timeSheetModel.setProcess(processModel);
                timeSheetModel.setStatus(statusModel);
                timeSheetModel.setProject(projectModel);
                timeSheetModel.setUser(userModel);
                timeSheetModels.add(timeSheetModel);
            }
            return timeSheetModels;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    public static int insertTimeSheetRecord(long UserId, long ProjectId,
			int WorkId, int ProcessId, int ProductId, String Date, float Time, String Description){
        CallableStatement cstmt = null;
        Connection conn = DataUtil.getConnection();
        int isSuccess = 1;
        try {
            cstmt = conn.prepareCall("{ call usp_Insert_Record_Timesheet(?,?,?,?,?,?,?,?)}");
            cstmt.setLong(1, UserId);
            cstmt.setLong(2, ProjectId);
            cstmt.setInt(3, ProductId);
            cstmt.setInt(4, ProcessId);
            cstmt.setInt(5, WorkId);
            cstmt.setString(6, Date);
            cstmt.setFloat(7, Time);
            cstmt.setString(8, Description);
            isSuccess = DataUtil.executeNonStore(cstmt);
            return isSuccess;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return isSuccess;
    }

    public static void insertTimeSheetList(ArrayList<TimeSheetModel> timeSheetList){    	
    	for (TimeSheetModel object : timeSheetList) {
    		CallableStatement cstmt = null;
            Connection conn = DataUtil.getConnection();
            
            try {
                cstmt = conn.prepareCall("{ call usp_Insert_Record_Timesheet(?,?,?,?,?,?,?,?)}");
                cstmt.setLong(	1, object.getUser().getUserId());
                cstmt.setLong(	2, object.getProject().getProjectId());
                cstmt.setInt(	3, object.getProduct().getProductId());
                cstmt.setInt(	4, object.getProcess().getProcessId());
                cstmt.setInt(	5, object.getWork().getWorkId());
                cstmt.setString(6, object.getDate());
                cstmt.setDouble(7, object.getTime());
                cstmt.setString(8, object.getDescription());
                
                DataUtil.executeNonStore(cstmt);          
            } catch (Exception e) {
                e.printStackTrace();
            }
    	}    	                
    }
    public static int removeTimeSheetById(Long id) {
        int isSuccess = 0;
        try {
            CallableStatement cstm = DataUtil.getConnection().prepareCall("{ call usp_DeleteTimeSheetByID(?) }");
            cstm.setLong(1, id);
            isSuccess = cstm.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return isSuccess;
    }

    public static void updateTimeSheetStatusById(Long timeSheetId, int statusId) {
        try {
            CallableStatement cstm = DataUtil.getConnection().prepareCall("{ call usp_UpdateTimeSheetStatusByTimeSheetID(?, ?) }");
            cstm.setLong(1, timeSheetId);
            cstm.setInt(2, statusId);
            DataUtil.executeNonStore(cstm);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static int updateTimeSheetById(Long timeSheetId, long ProjectId,
            int WorkId, int ProcessId, int ProductId, String Date, float Time, String Description) {
        CallableStatement cstmt = null;
        Connection conn = DataUtil.getConnection();
        int isSuccess = 1;
        try {
            cstmt = conn.prepareCall("{ call usp_Update_Record_Timesheet(?,?,?,?,?,?,?,?)}");
            cstmt.setLong(1, timeSheetId);
            cstmt.setLong(2, ProjectId);
            cstmt.setInt(3, ProductId);
            cstmt.setInt(4, ProcessId);
            cstmt.setInt(5, WorkId);
            cstmt.setString(6, Date);
            cstmt.setFloat(7, Time);
            cstmt.setString(8, Description);
            isSuccess = DataUtil.executeNonStore(cstmt);
            return isSuccess;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return isSuccess;
    }

    /**
     * Processes export timesheet data in xml format.
     * @param ids contains timesheetIds in proper string format
     * @return InputStream the XML value as a stream
     */
    public static InputStream exportXML(String ids) {
        Connection conn = DataUtil.getConnection();

        try {
            CallableStatement cstm = conn.prepareCall("{ Call usp_TSExportXML(?) }");
            cstm.setString(1, ids);
            ResultSet rs = DataUtil.executeStore(cstm);
            if (rs.next()) {
                return rs.getSQLXML(1).getBinaryStream();
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        }

        return null;
    }
}
